# -*- coding: utf-8 -*-
# File : mysql_util.py
# Author: taoyahui
# Date : 2021/3/23

import pymysql
import configparser
import os
import logging
from utils import common_util
import sys
from utils.path_util import get_root_dir
cf = configparser.ConfigParser()
cf.read(os.path.join(get_root_dir(),"config.ini"))

secs = cf.sections()
host = cf.get("Mysql-Database", "host")
port = cf.get("Mysql-Database", "port")
user = cf.get("Mysql-Database", "user")
password = cf.get("Mysql-Database", "password")
db = cf.get("Mysql-Database", "db")


def get_conn():
    conn = pymysql.connect(host=host, port=int(port), user=user, passwd=password, charset='utf8', db=db)
    return conn


def get_cursor(conn):
    return conn.cursor()


def exec_sql(conn, cursor, sql):
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        conn.commit()
        return result
    except BaseException as e:
        conn.rollback()
        logging.log(logging.ERROR, common_util.gen_error_msg(sys._getframe().f_code.co_name, e))


def exec_sql_new(sql):
    in_conn = get_conn()
    in_cursor = get_cursor(in_conn)
    try:
        in_cursor.execute(sql)
        result = in_cursor.fetchall()
        in_conn.commit()
        return result
    except BaseException as e:
        in_conn.rollback()
        logging.log(logging.ERROR, common_util.gen_error_msg(sys._getframe().f_code.co_name, e))
    finally:
        release(in_conn, in_cursor)


# params传入参数为字典的value
def exec_sql_params(sql, params):
    try:

        in_conn = get_conn()
        in_cursor = get_cursor(in_conn)
        in_cursor.execute(sql, params)
        result = in_cursor.fetchall()
        in_conn.commit()
        return result
    except BaseException as e:
        in_conn.rollback()
        print(sql)
        logging.log(logging.ERROR, common_util.gen_error_msg(sys._getframe().f_code.co_name, e))
    finally:
        in_conn.close()


def release(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()


def generate_insert_sql(data, table):

    keys = ', '.join(data.keys())
    values = ', '.join(['%s'] * len(data))
    sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
    return sql


if __name__ == '__main__':
    conn = get_conn()
    cursor = get_cursor(conn)

    # sql = "select job_state from apscheduler_jobs"
    # results = exec_sql_new(sql)
    # with open('./file.txt', 'wb') as f:
    #     f.write(results[0][0])
    #
    #
    # print(results)
    # cve_id = 'CVE-2019-3846'
    # sql = f"select count(1) from cve where cve_id = '{cve_id}'"
    # print(exec_sql(conn, cursor, sql)[0][0])
    # data = {
    #     'patch_no': 'RHSA-001',
    #     'name': 'Bob',
    #     'age': 20
    # }
    # table = 'students'
    # sql = generate_insert_sql(data, table)

